Second Normal Form

Second normal form (2NF)#

To be in second normal form, a relation must be in first normal form (1NF) and it must not contain any partial dependencies. So a relation is in 2NF as long as it has no partial dependencies, i.e., no non-prime attributes (attributes which are not part of any candidate key) is dependent on any proper subset of a composite primary key of the table.

Example#

STUDENT Relation

We can determine a few things by looking at the table above. First of all, Course_Fee alone cannot be used to identify each tuple uniquely. Furthermore, the combination of Course_Fee together with Stud_Id or Course_Id also cannot be used to uniquely identify each tuple. Hence, Course_Fee would be a non-prime attribute, as it does not belong to the composite primary key {Stud_Id, Course_Id}.

However, from the table, it is evident that Course_Id $\rightarrow$ Course_Fee , i.e., Course_fee is dependent on Course_Id only, which is a proper subset of the primary key. This results in a partial dependency and so this relation is not in 2NF.

To convert the above relation to 2NF, we need to split the table into two other tables such as:

• Table 1: Stud_Id, Course_Id

• Table 2: Course_Id, Course_Fee

In the first table, we keep Course_Id as the foreign key so that we can link the two tables together. This allows us to fetch the fee of a particular course from table 2.

It is important to note that 2NF tries to reduce the redundant data being stored in memory. For instance, if 100 students are taking the C1 course, we don’t need to store its fee for all 100 records (tuples). Instead, we can store it in the second table just once.

In the next lesson, we will discuss the concepts behind the third normal form (3NF).